﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC08BO : Do tuoi
    /// </summary>
    public class VYC08BO : DBConnectionBO
    {
        public VYC08BO()
            : base()
        {
            //
        }

        /// <summary>
        /// Lay danh sach cac du lieu trong VYC04
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC08> GetAllList()
        {
            var list = new List<ModelVYC08>();
            const string query = "SELECT [Id],[Ten],[TuoiTu],[TuoiDen],[NgayCapNhat],[TrangThai] FROM [VYC-08]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC08();

                model.Id = Convert.ToInt32(dataTable.Rows[i][0]);
                model.Ten = dataTable.Rows[i][1].ToString();
                model.TuoiTu = Convert.ToInt32(dataTable.Rows[i][2]);
                model.TuoiDen = Convert.ToInt32(dataTable.Rows[i][3]);
                model.NgayCapNhat = (DateTime)dataTable.Rows[i][4];
                model.TrangThai = Convert.ToInt32(dataTable.Rows[i][5]);

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC08 model)
        {
            var query = "INSERT INTO [VYC-08]([Ten],[TuoiTu],[TuoiDen],[NgayCapNhat],[TrangThai]) VALUES " +
                        "(@ten, @tuoitu,@tuoiden, GETDATE(), @trangthai)";
            var sqlCommand = new SqlCommand(query, connection);

            sqlCommand.Parameters.Add("@ten", SqlDbType.NVarChar).Value = model.Ten;
            sqlCommand.Parameters.Add("@tuoitu", SqlDbType.Int).Value = model.TuoiTu;
            sqlCommand.Parameters.Add("@tuoiden", SqlDbType.Int).Value = model.TuoiDen;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC08 model)
        {
            var query = "UPDATE [VYC-08] SET " +
                        "[Ten] = @ten" +
                        ",[TuoiTu] = @tuoitu" + 
                        ",[TuoiDen] = @tuoiden" +
                        ",NgayCapNhat = GETDATE(), TrangThai = @trangthai" +
                        " WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);

            sqlCommand.Parameters.Add("@ten", SqlDbType.NVarChar).Value = model.Ten;
            sqlCommand.Parameters.Add("@tuoitu", SqlDbType.Int).Value = model.TuoiTu;
            sqlCommand.Parameters.Add("@tuoiden", SqlDbType.Int).Value = model.TuoiDen;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@id", SqlDbType.Bit).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC08 model)
        {
            var query = "DELETE FROM [VYC-08] " +
                        " WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.Bit).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        public List<ModelVYC08> GetById(int id)
        {
            var list = new List<ModelVYC08>();
            var query = "SELECT * FROM [VYC-08] WHERE Id = " + id;
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC08();

                model.Id = Convert.ToInt32(dataTable.Rows[i][0]);
                model.Ten = dataTable.Rows[i][1].ToString();
                model.TuoiTu = Convert.ToInt32(dataTable.Rows[i][2]);
                model.TuoiDen = Convert.ToInt32(dataTable.Rows[i][3]);
                model.NgayCapNhat = (DateTime)dataTable.Rows[i][4];
                model.TrangThai = Convert.ToInt32(dataTable.Rows[i][5]);

                list.Add(model);
            }
            return list;
        }
    }
}